package com.stadium.service.impl;

import com.stadium.common.config.ConfigProperties;
import com.stadium.common.excel.ExcelTemplate;
import com.stadium.dto.BasicsRecordDto;
import com.stadium.dto.GameEventDto;
import com.stadium.dto.GameScheduleDto;
import com.stadium.dto.StarterDto;
import com.stadium.mapper.BasicsRecordMP;
import com.stadium.mapper.GameEventMP;
import com.stadium.mapper.GameScheduleMP;
import com.stadium.service.BasicsRecordSV;
import com.stadium.vo.searchVo.BasicsRecordSearchVo;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFAbstractNum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author WeiShunli on 2021/6/28
 */
@Service
public class RecordExcelExport {

    private static Logger logger = LoggerFactory.getLogger(RecordExcelExport.class);

    @Autowired
    private GameScheduleMP gameScheduleMP;
    @Autowired
    private BasicsRecordMP basicsRecordMP;
    @Autowired
    private GameEventMP gameEventMP;
    @Autowired
    private BasicsRecordSV basicsRecordSV;

    @Autowired
    private ConfigProperties configPropertiesAutowired;
    private static ConfigProperties configProperties;

    @PostConstruct
    public void init() {
        configProperties = this.configPropertiesAutowired;
    }

    public void createExcel(File file,String scheduleId){

        InputStream in = this.getClass().getResourceAsStream("/赛后统计.xlsx");
        try {
            Workbook workbook = new XSSFWorkbook(in);
            GameScheduleDto gameScheduleDto = gameScheduleMP.get(scheduleId);
            if (gameScheduleDto == null) {
                return;
            }
            GameEventDto gameEventDto = gameEventMP.get(gameScheduleDto.getEventId());
            if (gameEventDto == null) {
                return;
            }
            //创建sheet
            workbook.setSheetName(0, "比赛统计");
            Sheet sheet = workbook.getSheet("比赛统计");
            sheet.getRow(1).getCell(1).setCellValue(gameEventDto.getName());
            sheet.getRow(2).getCell(1).setCellValue(gameScheduleDto.getHometeamName()+"VS"+gameScheduleDto.getGuestteamName());
            sheet.getRow(3).getCell(1).setCellValue(gameScheduleDto.getHometeamName());
            sheet.getRow(3).getCell(3).setCellValue(gameScheduleDto.getGuestteamName());
            SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:MM");
            String str=sdf.format(gameScheduleDto.getCompetitionStarttime());
            String[] s = str.split(" ");
            sheet.getRow(4).getCell(1).setCellValue(s[0]);
            sheet.getRow(4).getCell(3).setCellValue(s[1]);
            //半场全场比分
            BasicsRecordSearchVo searchVo = new BasicsRecordSearchVo();
            searchVo.setScheduleId(scheduleId);
            searchVo.setPeriod(1);
            searchVo.setTeamId(gameScheduleDto.getHometeamId());
            int homeTeamHalfScore = basicsRecordSV.getTeamScore(searchVo);
            searchVo.setTeamId(gameScheduleDto.getGuestteamId());
            int guestTeamHalfScore = basicsRecordSV.getTeamScore(searchVo);
            searchVo.setPeriod(null);
            searchVo.setTeamId(gameScheduleDto.getHometeamId());
            int homeTeamScore = basicsRecordSV.getTeamScore(searchVo);
            searchVo.setTeamId(gameScheduleDto.getGuestteamId());
            int guestTeamScore = basicsRecordSV.getTeamScore(searchVo);
            sheet.getRow(5).getCell(1).setCellValue(homeTeamHalfScore+":"+guestTeamHalfScore);
            sheet.getRow(5).getCell(3).setCellValue(homeTeamScore+":"+guestTeamScore);
            sheet.getRow(8).getCell(1).setCellValue(gameScheduleDto.getHometeamName());
            sheet.getRow(8).getCell(3).setCellValue(gameScheduleDto.getGuestteamName());

            BasicsRecordSearchVo basicsRecordSearchVo = new BasicsRecordSearchVo();
            basicsRecordSearchVo.setScheduleId(scheduleId);
            basicsRecordSearchVo.setTeamId(gameScheduleDto.getHometeamId());
            List<StarterDto> homeStarter = basicsRecordSV.getStarter(basicsRecordSearchVo);
            if (homeStarter != null) {
                for(int i = 0; i < homeStarter.size();i++){
                    StarterDto starterDto = homeStarter.get(i);
                    sheet.getRow(i+9).getCell(0).setCellValue(starterDto.getJerseyNum());
                    sheet.getRow(i+9).getCell(1).setCellValue(starterDto.getName());
                    sheet.getRow(i+9).getCell(2).setCellValue(starterDto.getIsStarter() ==true ? "√" :"O");
                }
            }
            basicsRecordSearchVo.setTeamId(gameScheduleDto.getGuestteamId());
            List<StarterDto> guestStarter = basicsRecordSV.getStarter(basicsRecordSearchVo);
            if (guestStarter != null) {
                for(int i = 0; i < guestStarter.size();i++){
                    StarterDto starterDto = guestStarter.get(i);
                    sheet.getRow(i+9).getCell(3).setCellValue(starterDto.getJerseyNum());
                    sheet.getRow(i+9).getCell(4).setCellValue(starterDto.getName());
                    sheet.getRow(i+9).getCell(5).setCellValue(starterDto.getIsStarter() ==true ? "√" :"O");
                }
            }

            //5.输出
            FileOutputStream fileOutputStream = new FileOutputStream(file);
                workbook.write(fileOutputStream);
                fileOutputStream.close();
                workbook.close();
                return;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            logger.warn("Excel导出出错", e);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }



    /***
    * @Description: 创建事件记录表
    * @Param: file
    * @return:
    * @Author: WeiShunli
    * @Date: 2021/6/30
    */

    public void createRecordExcel(File file,String scheduleId){
        GameScheduleDto gameScheduleDto = gameScheduleMP.get(scheduleId);
        if (gameScheduleDto == null) {
            return;
        }
        //创建工作簿 new XSSFWorkbook();
        Workbook workbook = new XSSFWorkbook();
        //创建sheet
        Sheet sheet1 = workbook.createSheet(gameScheduleDto.getHometeamName());
        //创建表头
        this.createHeader(workbook,sheet1);
        Font font1 = this.createFont(workbook, false, (short) 14);
        CellStyle defCellStyle = this.createStyle(workbook, font1, false);

        if (gameScheduleDto != null) {
            BasicsRecordSearchVo basicsRecordSearchVo = new BasicsRecordSearchVo();
            basicsRecordSearchVo.setScheduleId(scheduleId);
            basicsRecordSearchVo.setTeamId(gameScheduleDto.getHometeamId());
            List<BasicsRecordDto> basicsRecordDtoList = basicsRecordMP.queryRecord(basicsRecordSearchVo);
            if (basicsRecordDtoList != null) {
                for(int i = 0; i <= basicsRecordDtoList.size() - 2;i++){
                    BasicsRecordDto record = basicsRecordDtoList.get(i);
                    Row row = sheet1.createRow(i+2);
                    row.setHeight((short) (20 * 20));
                    this.createCell(row, 0, String.valueOf(record.getOccurrenceTime()), defCellStyle);
                    this.createCell(row, 1, record.getTeamName(), defCellStyle);
                    this.createCell(row, 2, record.getCode(), defCellStyle);
                    String jerseyNum = record.getJerseyNum() != null ? record.getJerseyNum() : "-";
                    String name = record.getName() != null ? record.getName() : "-";
                    this.createCell(row, 3,  jerseyNum+"/"+name, defCellStyle);
                    this.createCell(row, 4, String.valueOf(record.getScore()), defCellStyle);
                    this.createCell(row, 5, "0", defCellStyle);
                }
            }
        }
        //生成客队事件记录sheet
        Sheet sheet2 = workbook.createSheet(gameScheduleDto.getGuestteamName());
        //创建表头
        this.createHeader(workbook,sheet2);

        if (gameScheduleDto != null) {
            BasicsRecordSearchVo basicsRecordSearchVo = new BasicsRecordSearchVo();
            basicsRecordSearchVo.setScheduleId(scheduleId);
            basicsRecordSearchVo.setTeamId(gameScheduleDto.getGuestteamId());
            List<BasicsRecordDto> basicsRecordDtoList = basicsRecordMP.queryRecord(basicsRecordSearchVo);
            if (basicsRecordDtoList != null) {
                for(int i = 0; i <= basicsRecordDtoList.size() - 2;i++){
                    BasicsRecordDto record = basicsRecordDtoList.get(i);
                    Row row = sheet2.createRow(i+2);
                    row.setHeight((short) (20 * 20));
                    this.createCell(row, 0, String.valueOf(record.getOccurrenceTime()), defCellStyle);
                    this.createCell(row, 1, record.getTeamName(), defCellStyle);
                    this.createCell(row, 2, record.getCode(), defCellStyle);
                    String jerseyNum = record.getJerseyNum() != null ? record.getJerseyNum() : "-";
                    String name = record.getName() != null ? record.getName() : "-";
                    this.createCell(row, 3,  jerseyNum+"/"+name, defCellStyle);
                    this.createCell(row, 4, "0", defCellStyle);
                    this.createCell(row, 5, String.valueOf(record.getScore()), defCellStyle);
                }
            }
        }
        //5.输出
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            workbook.close();
            return;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            logger.warn("Excel导出出错", e);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }




    private void createHeader(Workbook workbook,Sheet sheet){

        //1.设置列宽
        this.setRecordColumns(sheet);

        //创建行
        //设置表名称
        Row row0 = sheet.createRow(0);
        row0.setHeight((short) (30 * 20));
        Font font = this.createFont(workbook, true, "黑体",(short) 16);
        XSSFCellStyle cellStyle = (XSSFCellStyle)this.createStyle(workbook, font, false);
        XSSFColor myColor = new XSSFColor(java.awt.Color.blue);
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色

        this.createCell(row0, 0, "事件细节", cellStyle);
        this.createCell(row0, 3, "请清楚阐述", cellStyle);
        this.createCell(row0, 4, "事件得分", cellStyle);

        //
        Font font1 = this.createFont(workbook, false, (short) 14);
        CellStyle defCellStyle = this.createStyle(workbook, font1, false);
        Row row1 = sheet.createRow(1);
        row1.setHeight((short) (20 * 20));
        this.createCell(row1, 0, "时间", defCellStyle);
        this.createCell(row1, 1, "队名", defCellStyle);
        this.createCell(row1, 2, "事件", defCellStyle);
        this.createCell(row1, 3, "球员号码及姓名", defCellStyle);
        this.createCell(row1, 4, "队名A", defCellStyle);
        this.createCell(row1, 5, "队名B", defCellStyle);
        List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,2);
        cellRangeAddressList.add(cellRangeAddress);
        cellRangeAddress = new CellRangeAddress(0,0,4,5);
        cellRangeAddressList.add(cellRangeAddress);
        this.mergeCell(sheet,cellRangeAddressList);
    }



    public String exportTemplate(File file,String scheduleId) {
        InputStream in = this.getClass().getResourceAsStream("/记录模板.xlsx");
        String fileName ="";
        try {
            XSSFWorkbook tpWorkbook = new XSSFWorkbook(in);
            // 新建一个Excel的工作空间
            XSSFWorkbook workbook = new XSSFWorkbook();
            workbook = tpWorkbook;
            GameScheduleDto gameScheduleDto = gameScheduleMP.get(scheduleId);
            if (gameScheduleDto == null) {
                return "";
            }
            //创建sheet
            workbook.setSheetName(0, gameScheduleDto.getHometeamName());
            Sheet sheet1 = workbook.getSheet(gameScheduleDto.getHometeamName());

            Font font1 = this.createFont(workbook, false, (short) 14);
            //CellStyle defCellStyle = this.createStyle(workbook, font1, false);

            if (gameScheduleDto != null) {
                BasicsRecordSearchVo basicsRecordSearchVo = new BasicsRecordSearchVo();
                basicsRecordSearchVo.setScheduleId(scheduleId);
                basicsRecordSearchVo.setTeamId(gameScheduleDto.getHometeamId());
                List<BasicsRecordDto> basicsRecordDtoList = basicsRecordMP.queryRecord(basicsRecordSearchVo);
                if (basicsRecordDtoList != null) {
                    for(int i = 0; i < basicsRecordDtoList.size();i++){
                        BasicsRecordDto record = basicsRecordDtoList.get(i);
                        Row row = sheet1.getRow(i+2);
                        row.getCell(0).setCellValue(String.valueOf(record.getOccurrenceTime()));
                        row.getCell(1).setCellValue(record.getTeamName());
                        row.getCell(2).setCellValue(record.getCode());
                        row.getCell(3).setCellValue(record.getContent());
                        String jerseyNum = record.getJerseyNum() != null ? record.getJerseyNum() : "-";
                        String name = record.getName() != null ? record.getName() : "-";
                        row.getCell(4).setCellValue(jerseyNum+"/"+name);
                        row.getCell(5).setCellValue(String.valueOf(record.getScore()));
                    }
                }
            }
            //创建sheet
            workbook.setSheetName(1, gameScheduleDto.getGuestteamName());
            Sheet sheet2 = workbook.getSheet(gameScheduleDto.getGuestteamName());

            if (gameScheduleDto != null) {
                BasicsRecordSearchVo basicsRecordSearchVo = new BasicsRecordSearchVo();
                basicsRecordSearchVo.setScheduleId(scheduleId);
                basicsRecordSearchVo.setTeamId(gameScheduleDto.getGuestteamId());
                List<BasicsRecordDto> basicsRecordDtoList = basicsRecordMP.queryRecord(basicsRecordSearchVo);
                if (basicsRecordDtoList != null) {
                    for(int i = 0; i < basicsRecordDtoList.size();i++){
                        BasicsRecordDto record = basicsRecordDtoList.get(i);
                        Row row = sheet2.getRow(i+2);
                        row.getCell(0).setCellValue(String.valueOf(record.getOccurrenceTime()));
                        row.getCell(1).setCellValue(record.getTeamName());
                        row.getCell(2).setCellValue(record.getCode());
                        row.getCell(3).setCellValue(record.getContent());
                        String jerseyNum = record.getJerseyNum() != null ? record.getJerseyNum() : "-";
                        String name = record.getName() != null ? record.getName() : "-";
                        row.getCell(4).setCellValue(jerseyNum+"/"+name);
                        row.getCell(5).setCellValue(String.valueOf(record.getScore()));
                    }
                }
            }
            /*FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            workbook.close();*/

            //生成文件
            fileName = "/赛后记录.xlsx";
            String path = configProperties.getUploadFileUrl() + configProperties.getFileUrl()+ configProperties.getUploadFileSymbol() + "excel";
            File fileF = new File(path);
            // 判断上传文件的保存目录是否存在
            if (!fileF.exists()) {
                // 创建目录
                fileF.mkdirs();
            }

            FileOutputStream fout = new FileOutputStream(path+fileName);
            try {
                // 将数据写出去
                workbook.write(fout);
                String str = "导出" + file + "成功！";
            } catch (Exception e) {
                e.printStackTrace();
                String str1 = "导出" + file + "失败！";
            } finally {
                fout.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return configProperties.getFileUrl()+ configProperties.getUploadFileSymbol() + "excel"+fileName;
    }

    /**
     * 创建单元格样式
     * @param workbook
     * @param font
     * @param border
     * @return
     */
    public static CellStyle createStyle(Workbook workbook, Font font, boolean border){
        CellStyle cellStyle = workbook.createCellStyle();
        //水平对齐方式 居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置字体
        cellStyle.setFont(font);
        //垂直方向对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //单元格内容换行 根据宽度换行
        cellStyle.setWrapText(true);
        //设置单元格的边框
        if(border){
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
        }
        return cellStyle;

    }

    /**
     * 创建字体
     * @param workbook
     * @param bold
     * @param fontName
     * @param fontSize
     * @return
     */
    public static Font createFont(Workbook workbook,boolean bold,String fontName,short fontSize){
        Font font = workbook.createFont();
        font.setFontName(fontName);
        //字体加粗
        font.setBold(bold);
        //字体颜色
        font.setColor(Font.COLOR_NORMAL);
        //设置字体大小
        font.setFontHeightInPoints(fontSize);
        return font;
    }

    /**
     * 创建默认宋体的字体
     * @param workbook
     * @param bold
     * @param fontSize
     * @return
     */
    public static Font createFont(Workbook workbook,boolean bold,short fontSize){
        return createFont(workbook,bold,"宋体",fontSize);
    }

    /**
     * 单元格
     * @param row 当前行
     * @param i 列
     * @param value 值
     * @param cellStyle 单元格样式
     * @return
     */
    public static Cell createCell(Row row, int i, String value, CellStyle cellStyle){
        Cell cell = row.createCell(i);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
        return cell;
    }


    /**
     * 统一设置合并的单元格
     * @param sheet
     * @param cellRangeAddressList
     */
    public void  mergeCell(Sheet sheet, List<CellRangeAddress> cellRangeAddressList){
        for (CellRangeAddress cellRangeAddress: cellRangeAddressList) {
            sheet.addMergedRegion(cellRangeAddress);
        }
    }

    //设置列宽
    public void setColumns(Sheet sheet) {
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 30 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 10 * 256);
        sheet.setColumnWidth(4, 30 * 256);
        sheet.setColumnWidth(5, 10 * 256);
    }

    //设置事件记录表列宽
    public void setRecordColumns(Sheet sheet) {
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 30 * 256);
        sheet.setColumnWidth(4, 10 * 256);
        sheet.setColumnWidth(5, 10 * 256);
    }
}
